package com.hyjx.framework.component.excel;

import com.hyjx.framework.common.CommonUtil;
import com.hyjx.framework.common.util.SpringBeanUtil;
import com.hyjx.framework.component.customquery.CustomQueryService;
import com.hyjx.framework.component.message.MessageService;
import com.hyjx.framework.service.ConfigManager;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * excel导入导出的实现类
 */
@Controller
@RequestMapping("/excel")
public class ExcelController {
    @Resource
    private CustomQueryService queryservice;
    @Resource
    private MessageService msgservice;

    /**
     * 进入到信息查询页
     *
     * @return
     */
    @RequestMapping(value = "/gotoImport", method = RequestMethod.GET)
    public String gotoImport(HttpServletRequest request) {
        request.setAttribute("en_module_name", request.getParameter("en_module_name"));
        return "/framework/common/importxls";
    }

    /**
     * 执行导入
     *
     * @param request
     * @return
     */
    @RequestMapping(value = "/doImport", method = RequestMethod.POST)
    @ResponseBody
    public String doImport(MultipartHttpServletRequest request) {

        String t = request.getParameter("t");
        Iterator<String> iterator = request.getFileNames();
        while (iterator.hasNext()) {
            String fileName = iterator.next();
            MultipartFile multipartFile = request.getFile(fileName);
            try {
                String filename = multipartFile.getOriginalFilename();
                String path = ConfigManager.getAttatchmentsPath();
                File upload = new File(path);
                if (!upload.exists())
                    upload.mkdirs();
                File source = new File(path + filename);
                multipartFile.transferTo(source);
                readxls(source, t);
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
        return "/framework/common/importxls";
    }

    /**
     * 读取excel数据，存入数据库
     *
     * @param file
     * @param table
     */
    @SuppressWarnings("rawtypes")
    private void readxls(File file, String table) {
        FileInputStream fi = null;
        POIFSFileSystem fs = null;
        try {

            if (file.getName().toLowerCase().endsWith(".xls")) {
                fi = new FileInputStream(file);
                fs = new POIFSFileSystem(fi);
                HSSFWorkbook wb = new HSSFWorkbook(fs);
            } else {
                XSSFWorkbook wb = null;
                try {
                    wb = new XSSFWorkbook(file.getAbsolutePath());
                    XSSFSheet sheet = wb.getSheetAt(0);
                    List<Map<String, String>> cols = queryservice.getColumns("select * from " + table + " where 1=2 ");
                    StringBuilder sb = new StringBuilder();
                    sb.append("insert into ").append(table).append("(");
                    for (Map<String, String> map : cols) {
                        sb.append(map.get("COLUMN_NAME")).append(",");
                    }
                    int index = sb.append(")").indexOf(",)");
                    sb.setCharAt(index, ' ');
                    sb.append(" values(");
                    StringBuilder sbsql = new StringBuilder();
                    for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                        //MessageUserModel model = new MessageUserModel();
                        XSSFRow row = sheet.getRow(i);
                        Iterator it = row.iterator();
                        sbsql.append(sb.toString()).append("'" + CommonUtil.getUUID32() + "',");
                        //model.setMuid(CommonUtil.getUUID32());
                        int a = 0;
                        while (it.hasNext()) {
                            a++;
                            XSSFCell cell = (XSSFCell) it.next();
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            String val = cell.getStringCellValue();
//							if(a==1)
//								model.setMsgid(val);
//							else if(a==2)
//								model.setUserid(val);
//							else if(a==3)
//								model.setIsread(val);
                            //else if(a==4)
                            //	model.setReadtime(val);
                            sbsql.append("'" + val + "',");

                        }
                        sbsql.append(")");
                        int endindex = sbsql.indexOf(",)");
                        sbsql.setCharAt(endindex, ' ');
                        sbsql.append(";");
                        //msgservice.addmsgusr(model);

                    }
                    JdbcTemplate jt = (JdbcTemplate) SpringBeanUtil.getBean("jdbcTemplate");
                    jt.batchUpdate(sbsql.toString().split(";"));
                } catch (Exception ex) {
                    ex.printStackTrace();
                } finally {
                    if (wb != null) {
                        wb.close();
                    }
                }
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (fi != null)
                    fi.close();
            } catch (IOException e) {
                e.printStackTrace();
            }

        }

    }
}